﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Administration.Catalog
{
    public partial class CompetencyRepo : ISearchable<Competency>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column,string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM master.Competency {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM master.Competency {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM master.Competency {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM master.Competency {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM master.Competency {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM master.Competency {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<Competency> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<Competency> tt = new List<Competency>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      ComptID, Rowstatus, CompetncyDesc, Definition, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      master.Competency ");
                    sbQuery.AppendFormat("      {0} ",parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                Competency t = new Competency();
                                t.ComptID = dr[Competency.Fields.ComptID] == DBNull.Value ? 0 : int.Parse(dr[Competency.Fields.ComptID].ToString());
                                t.RowStatus = dr[Competency.Fields.RowStatus] == DBNull.Value ? false : bool.Parse(dr[Competency.Fields.RowStatus].ToString());
                                t.CompetncyDesc = dr[Competency.Fields.CompetncyDesc] == DBNull.Value ? string.Empty : dr[Competency.Fields.CompetncyDesc].ToString();
                                t.Definition = dr[Competency.Fields.Definition] == DBNull.Value ? string.Empty : dr[Competency.Fields.Definition].ToString();
                                t.CreatedBy = dr[Competency.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[Competency.Fields.CreatedBy].ToString();
                                t.CreatedDate = dr[Competency.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Competency.Fields.CreatedDate].ToString());
                                t.UpdatedBy = dr[Competency.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[Competency.Fields.UpdatedBy].ToString();
                                t.UpdatedDate = dr[Competency.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[Competency.Fields.UpdatedDate].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(Competency domain,List<LevelCompetency> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Insert Data Competency

                    string query = "INSERT INTO master.Competency(RowStatus, CompetncyDesc, Definition, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate) VALUES(@RowStatus, @CompetncyDesc, @Definition, @CreatedBy, @CreatedDate, @UpdatedBy, @UpdatedDate)";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = domain.RowStatus;
                    cmd.Parameters.Add("@CompetncyDesc", SqlDbType.VarChar).Value = (object)domain.CompetncyDesc ?? DBNull.Value;
                    cmd.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)domain.Definition ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Insert Data Level Competency


                    #region Ambil CompetencyID 

                    string querycompid = @"SELECT ComptID FROM master.Competency WHERE CreatedBy=@CreatedBy AND CreatedDate=@CreatedDate";


                    SqlCommand cmdComptID = new SqlCommand(querycompid, conn);
                    cmdComptID.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdComptID.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = domain.CreatedDate;
                    cmdComptID.Transaction = txn;
                    var compid = cmdComptID.ExecuteScalar();
                    int resultcompid = compid == DBNull.Value ? 0 : int.Parse(compid.ToString());
                    cmdComptID.Dispose();

                    #endregion


                    foreach (LevelCompetency data in list)
                    {
                       
                        query = "INSERT INTO master.LevelCompetency(CompetencyID, Level, Definition, CreatedBy, CreatedDate, UpdatedDate, UpdatedBy) VALUES(@CompetencyID, @Level, @Definition, @CreatedBy, @CreatedDate, @UpdatedDate, @UpdatedBy)";
                        SqlCommand cmdLevel = new SqlCommand(query, conn);
                        cmdLevel.CommandType = CommandType.Text;
                        cmdLevel.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = resultcompid;
                        cmdLevel.Parameters.Add("@Level", SqlDbType.SmallInt).Value = (object)data.Level ?? DBNull.Value;
                        cmdLevel.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)data.Definition ?? DBNull.Value;
                        cmdLevel.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        cmdLevel.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        cmdLevel.Parameters.Add("@UpdatedDate", SqlDbType.VarChar).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        cmdLevel.Parameters.Add("@UpdatedBy", SqlDbType.DateTime).Value = (object)data.UpdatedBy ?? DBNull.Value;

                        cmdLevel.Transaction = txn;
                        cmdLevel.ExecuteNonQuery();
                        cmdLevel.Dispose();
                    }

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool Update(Competency domain, List<LevelCompetency> list)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region UPDATE Data Competency

                    string query = "UPDATE master.Competency SET RowStatus = @RowStatus, CompetncyDesc = @CompetncyDesc, Definition = @Definition, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate WHERE ComptID = @ComptID";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ComptID", SqlDbType.Int).Value = domain.ComptID;
                    cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = domain.RowStatus;
                    cmd.Parameters.Add("@CompetncyDesc", SqlDbType.VarChar).Value = (object)domain.CompetncyDesc ?? DBNull.Value;
                    cmd.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)domain.Definition ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Ambil JumlahData berdasar LEVEL dan CompetencyID

                    query = @"SELECT * FROM master.LevelCompetency WHERE CompetencyID=@CompetencyID";
                    SqlCommand cmdGetDataDB = new SqlCommand(query, conn);
                    cmdGetDataDB.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = domain.ComptID;
                    cmdGetDataDB.Transaction = txn;

                    DataSet ds = new DataSet();
                    SqlDataAdapter adp = new SqlDataAdapter(cmdGetDataDB);

                    adp.Fill(ds);
                    adp.Dispose();

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        var queryLinq = list.Where(x => x.Level == Convert.ToInt16(dr["Level"].ToString()));
                        int jumlahLevel = queryLinq.Count();
                        if (jumlahLevel == 0)
                        {
                            #region Hapus Semua Level Sebelum Update

                            string querydelete = @"DELETE FROM master.LevelCompetency WHERE ID = @ID";

                            SqlCommand cmdDeleteCompt = new SqlCommand(querydelete, conn);
                            cmdDeleteCompt.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(dr["ID"].ToString());
                            cmdDeleteCompt.Transaction = txn;
                            cmdDeleteCompt.ExecuteNonQuery();
                            cmdDeleteCompt.Dispose();

                            #endregion
                        }

                    }

                    cmdGetDataDB.Dispose();

                    #endregion

                    #region Insert Data Level Competency


                    foreach (LevelCompetency data in list)
                    {


                        #region Ambil JumlahData berdasar LEVEL dan CompetencyID
                        string querycompid = @"SELECT COUNT(*) FROM master.LevelCompetency WHERE Level=@Level AND CompetencyID=@CompetencyID";
                        SqlCommand cmdComptID = new SqlCommand(querycompid, conn);
                        cmdComptID.Parameters.Add("@Level", SqlDbType.SmallInt).Value =data.Level;
                        cmdComptID.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = domain.ComptID;
                        cmdComptID.Transaction = txn;
                        var compid = cmdComptID.ExecuteScalar();
                        int resultcompid = compid == DBNull.Value ? 0 : int.Parse(compid.ToString());
                        cmdComptID.Dispose();
                        #endregion

                     

                        if (resultcompid > 0)
                        {

                            #region Ambil LevelID berdasar LEVEL dan CompetencyID
                            string querylevelID = @"SELECT ID FROM master.LevelCompetency WHERE Level=@Level AND CompetencyID=@CompetencyID";

                            SqlCommand cmdlevelID = new SqlCommand(querylevelID, conn);
                            cmdlevelID.Parameters.Add("@Level", SqlDbType.SmallInt).Value = data.Level;
                            cmdlevelID.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = domain.ComptID;
                            cmdlevelID.Transaction = txn;
                            var levelid = cmdlevelID.ExecuteScalar();
                            int resultlevelid = levelid == DBNull.Value ? 0 : int.Parse(levelid.ToString());
                            cmdlevelID.Dispose();

                            #endregion


                            query= "UPDATE master.LevelCompetency SET Definition = @Definition, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ID = @ID";
                            SqlCommand cmdLevel = new SqlCommand(query, conn);
                            cmdLevel.Parameters.Add("@ID", SqlDbType.Int).Value = resultlevelid;
                            cmdLevel.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)data.Definition ?? DBNull.Value;
                            cmdLevel.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)data.UpdatedDate ?? DBNull.Value;
                            cmdLevel.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)data.UpdatedBy ?? DBNull.Value;

                            cmdLevel.Transaction = txn;
                            cmdLevel.ExecuteNonQuery();
                            cmdLevel.Dispose();
                        }
                        else
                        {
                            query = "INSERT INTO master.LevelCompetency(CompetencyID, Level, Definition, CreatedBy, CreatedDate) VALUES(@CompetencyID, @Level, @Definition, @CreatedBy, @CreatedDate)";
                            SqlCommand cmdLevelInsert = new SqlCommand(query, conn);
                            cmdLevelInsert.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = domain.ComptID;
                            cmdLevelInsert.Parameters.Add("@Level", SqlDbType.SmallInt).Value = (object)data.Level ?? DBNull.Value;
                            cmdLevelInsert.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)data.Definition ?? DBNull.Value;
                            cmdLevelInsert.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                            cmdLevelInsert.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;

                            cmdLevelInsert.Transaction = txn;
                            cmdLevelInsert.ExecuteNonQuery();
                            cmdLevelInsert.Dispose();
                           
                        }


                   


                        //query = "INSERT INTO master.LevelCompetency(CompetencyID, Level, Definition, CreatedBy, CreatedDate, UpdatedDate, UpdatedBy) VALUES(@CompetencyID, @Level, @Definition, @CreatedBy, @CreatedDate, @UpdatedDate, @UpdatedBy)";
                        //SqlCommand cmdLevel = new SqlCommand(query, conn);
                        //cmdLevel.CommandType = CommandType.Text;
                        //cmdLevel.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = domain.ComptID;
                        //cmdLevel.Parameters.Add("@Level", SqlDbType.SmallInt).Value = (object)data.Level ?? DBNull.Value;
                        //cmdLevel.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)data.Definition ?? DBNull.Value;
                        //cmdLevel.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)data.CreatedBy ?? DBNull.Value;
                        //cmdLevel.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)data.CreatedDate ?? DBNull.Value;
                        //cmdLevel.Parameters.Add("@UpdatedDate", SqlDbType.VarChar).Value = (object)data.UpdatedDate ?? DBNull.Value;
                        //cmdLevel.Parameters.Add("@UpdatedBy", SqlDbType.DateTime).Value = (object)data.UpdatedBy ?? DBNull.Value;

                        //cmdLevel.Transaction = txn;
                        //cmdLevel.ExecuteNonQuery();
                        //cmdLevel.Dispose();
                    }

              

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public bool DeleteWithLevel(int key)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Delete Table Competency

                    //string query = "UPDATE master.Competency SET RowStatus = @RowStatus, CompetncyDesc = @CompetncyDesc, Definition = @Definition, UpdatedBy = @UpdatedBy, UpdatedDate = @UpdatedDate WHERE ComptID = @ComptID";
                    //SqlCommand cmd = new SqlCommand(query, conn);
                    //cmd.CommandType = CommandType.Text;
                    //cmd.Parameters.Add("@ComptID", SqlDbType.Int).Value = domain.ComptID;
                    //cmd.Parameters.Add("@RowStatus", SqlDbType.Bit).Value = domain.RowStatus;
                    //cmd.Parameters.Add("@CompetncyDesc", SqlDbType.VarChar).Value = (object)domain.CompetncyDesc ?? DBNull.Value;
                    //cmd.Parameters.Add("@Definition", SqlDbType.Text).Value = (object)domain.Definition ?? DBNull.Value;
                    //cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    //cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;



                    string query = "DELETE FROM master.Competency WHERE ComptID = @ComptID";
                    SqlCommand cmd = new SqlCommand(query, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ComptID", SqlDbType.Int).Value = key;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    #endregion

                    #region Delete Table Level Competency


                    query = "DELETE FROM master.LevelCompetency WHERE CompetencyID = @CompetencyID";
                    SqlCommand cmdLevel = new SqlCommand(query, conn);
                    cmdLevel.CommandType = CommandType.Text;
                    cmdLevel.Parameters.Add("@CompetencyID", SqlDbType.Int).Value = key;

                    cmdLevel.Transaction = txn;
                    cmdLevel.ExecuteNonQuery();
                    cmdLevel.Dispose();

                    #endregion


                    result = true;
                    txn.Commit();

                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }

            return result;
        }

        public DataTable GetDataTableReport(string param)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();

                    sbQuery.AppendLine("SELECT");
                    sbQuery.AppendLine("B.CompetncyDesc Kompetensi");
                    sbQuery.AppendLine(",C.Level");
                    sbQuery.AppendLine(",D.PositionName Jabatan");
                    sbQuery.AppendLine(",E.DepartmentDesc Departemen");
                    sbQuery.AppendLine(",CASE WHEN B.GroupCompetency='CP' THEN 'Core Competency' ELSE 'Managerial Competency' END GroupKompetensi");
                    sbQuery.AppendLine("FROM master.JobManagerialCompetency A ");
                    sbQuery.AppendLine("INNER JOIN master.Competency B ON A.CompetencyID=B.ComptID");
                    sbQuery.AppendLine("INNER JOIN master.LevelCompetency C ON A.LevelCompetencyID = C.ID");
                    sbQuery.AppendLine("INNER JOIN master.Position D ON A.PositionID =D.PositionID");
                    sbQuery.AppendLine("INNER JOIN master.Department E ON D.DepartmentID = E.DeptID");
                    sbQuery.AppendLine(param);
                    //if (param.Trim().Length > 0)
                    //    sbQuery.AppendFormat("WHERE D.DepartmentID IN {0}",param);

                    cmd.CommandText = sbQuery.ToString();
                    //if (param.Trim().Length > 0)
                    //    cmd.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = param;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;

        }
    }
}
